<%@ page import="java.util.List" %>
<%@ page import="java.util.ArrayList" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.Statement" %>
<%@ page import="java.sql.ResultSet" %>
<%@ page isELIgnored="false" %>
<%@ page import="java.sql.DriverManager" %><%--
  Created by IntelliJ IDEA.
  User: Administrato
  Date: 2018/3/20 0020
  Time: 下午 4:47
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
  <head>
    <title>$Title$</title>
  </head>
  <body>
  <h1>这是一个JSP文件</h1>

  <%

    out.write("hello,world!");

    out.write("<h1>hello,world!</h1>");

  %>

  <style type="text/css">

    table {
      border: 1px solid #000;
      border-collapse: collapse;
    }

    td, th {
      border: 1px solid #000000;
      padding: 10px;
    }

  </style>


  <table>
    <thead>
    <tr>
      <th>编号</th>
      <th>用户名</th>
      <th>密码</th>
    </tr>
    </thead>

    <tbody>


    <%

      class User {
        int id;
        String name;
        String password;

        // alt + insert
        public User(int id, String name, String password) {
          this.id = id;
          this.name = name;
          this.password = password;
        }
      }


      List<User> list = new ArrayList<User>();

      // 3 + 1
      Connection connection = null;
      Statement statement = null;
      ResultSet resultSet = null;

      String sql = "select * from t_users";

      Class.forName("com.mysql.jdbc.Driver");

      connection = DriverManager.getConnection(
              "jdbc:mysql://localhost:3306/sctu-javaweb-2018",
              "root",
              "123456"
      );

      statement = connection.createStatement();

      resultSet = statement.executeQuery(sql);

      while(resultSet.next()){

        int id = resultSet.getInt(1);
        String username = resultSet.getString(2);
        String password = resultSet.getString(3);

        User user = new User(id, username, password);
        list.add(user);

      }
      resultSet.close();
      statement.close();
      connection.close();



      for (User user : list) {

        out.write("<tr><td>" + user.id + "</td><td>" + user.name
                + "</td><td>" + user.password + "</td></tr>");
      }

    %>


    </tbody>

    <tfoot>
    </tfoot>
  </table>

  </body>
</html>
